######### Notes ###########

# Files: Death Penalty - Corporal Punishment - Punitiveness,
#        Fear of Crime - Security Behaviour - Satisfaction
# Columns: Varname - Date - Index - Demographic - Poll - Question

######### CSEW ###########

library(tidyverse)

read_csv("/Users/matteo/Desktop/Prison Polling/Prison Data Internship/Survey Data/Finished/CSEW_allrepeatedcrimequestions.csv") %>%
  select(gender, age, race, weight, year, 
         quallife, qualif2, safewalkdark, safehomenight,
         crimeincr, crimeincr2, crimeincrUK, crimeincrUK2, crimconincr,
         worryburg, worrymug, worryrape, worrymolest, worryvand, worryattack, worryraceattacks,
         worryinsult, worrycarstolen, worrycartheft, worrycarvand, worryrape2,
         likelyvand, likelymug, likelyattack, likelyburg, likelyrape, likelymolest,
         likelycarstolen, likelycartheft, likelycarvand,
         commonteen, commongraff, commonvand,
         probteen, probvand, probraceattacks, probdrugs,
         crimcausepolsent, crimcausepolsent2, crimcausepolsent3,
         senttoolenient, juvtoolenient) %>%
  mutate(quallife = ifelse(quallife %in% c('10 - total effect','9','8','7','6'),1,
                           ifelse(is.na(quallife), NA_integer_, 0)),
         qualif2 = ifelse(qualif2 %in% c('10 - total effect','9','8','7','6'),1,
                          ifelse(is.na(qualif2), NA_integer_, 0)),
         safewalkdark = ifelse(safewalkdark %in% c('or very unsafe','bit unsafe',
                                                   'very unsafe','bitunsafe','veryunsafe',
                                                   'vunsafe','a bit unsafe','or very unsafe?'),1,
                               ifelse(is.na(safewalkdark), NA_integer_, 0)),
         safehomenight = ifelse(safehomenight %in% c('bitunsafe','veryunsafe',
                                                    'vunsafe','a bit unsafe','or very unsafe',
                                                    'very unsafe','or very unsafe?'),1,
                                ifelse(is.na(safehomenight), NA_integer_, 0)),
         crimeincr = ifelse(crimeincr %in% c('little more','lot more',
                                             'a little more','a lot more','alot more crime',
                                             'little more crime','a lot more crime', 'a little more crime'),1,
                                ifelse(is.na(crimeincr) | crimeincr == 'refusal, no answer', NA_integer_, 0)),
         crimeincr2 = ifelse(crimeincr2 %in% c('gone up a little',
                                             'gone up a lot'),1,
                            ifelse(is.na(crimeincr2), NA_integer_, 0)),
         crimeincrUK = ifelse(crimeincrUK %in% c('little more','lot more',
                                               'a little more crime','a lot more crime'),1,
                             ifelse(is.na(crimeincrUK), NA_integer_, 0)),
         crimeincrUK2 = ifelse(crimeincrUK2 %in% c('gone up a little',
                                                 'gone up a lot'),1,
                              ifelse(is.na(crimeincrUK2), NA_integer_, 0)),
         crimconincr = ifelse(crimconincr %in% c('increased'),1,
                               ifelse(is.na(crimconincr), NA_integer_, 0)),
         worryburg = ifelse(worryburg %in% c('very worried','fairly worried',
                                             'veryworried','fairlyworried',
                                             'vworried','fworried'),1,
                               ifelse(is.na(worryburg) | worryburg == 'refusal, no answer', NA_integer_, 0)),
         worrymug = ifelse(worrymug %in% c('very worried','fairly worried',
                                             'veryworried','fairlyworried',
                                             'vworried','fworried'),1,
                            ifelse(is.na(worrymug) | worrymug == 'refusal, no answer', NA_integer_, 0)),
         worryrape = ifelse(worryrape %in% c('very worried','fairly worried',
                                           'veryworried','fairlyworried',
                                           'vworried','fworried'),1,
                           ifelse(is.na(worryrape), NA_integer_, 0)),
         worrymolest = ifelse(worrymolest %in% c('very worried',
                                           'fairly worried'),1,
                           ifelse(is.na(worrymolest) | worrymolest == 'refusal, no answer', NA_integer_, 0)),
         worryvand = ifelse(worryvand %in% c('fairly worried','very worried',
                                           'veryworried',
                                           'fairlyworried'),1,
                           ifelse(is.na(worryvand), NA_integer_, 0)),
         worryattack = ifelse(worryattack %in% c('very worried','fairly worried',
                                             'veryworried'),1,
                            ifelse(is.na(worryattack) | worryattack == 'refusal, no answer', NA_integer_, 0)),
         worryraceattacks = ifelse(worryraceattacks %in% c('vworried','fworried',
                                             'very worried',
                                             'fairly worried'),1,
                            ifelse(is.na(worryraceattacks) | worryraceattacks == 'refusal, no answer', NA_integer_, 0)),
         worryinsult = ifelse(worryinsult %in% c('very worried','fairly worried',
                                             'vworried',
                                             'fworried'),1,
                            ifelse(is.na(worryinsult) | worryinsult == 'refusal, no answer', NA_integer_, 0)),
         worrycarstolen = ifelse(worrycarstolen %in% c('fairlyworried','veryworried',
                                                 'fworried','vworried',
                                                 'fairly worried','very worried'),1,
                              ifelse(is.na(worrycarstolen) | worrycarstolen %in% c('refusal, no answer','not applic'), NA_integer_, 0)),
         worrycartheft = ifelse(worrycartheft %in% c('fairlyworried','veryworried',
                                                       'fworried','vworried',
                                                       'fairly worried','very worried'),1,
                                 ifelse(is.na(worrycartheft) | worrycartheft %in% c('refusal, no answer','not applic'), NA_integer_, 0)),
         worrycarvand = ifelse(worrycarvand %in% c('fairlyworried','veryworried'),1,
                                ifelse(is.na(worrycarvand) | worrycarvand %in% c('refusal, no answer','not applic'), NA_integer_, 0)),
         worryrape2 = ifelse(worryrape2 %in% c('very worried','fairly worried'),1,
                                ifelse(is.na(worryrape2) | worryrape2 == 'refusal, no answer', NA_integer_, 0)),
         likelyvand = ifelse(likelyvand %in% c('Very likely','Fairly likely',
                                               'Certain to','fairly likely','very likely',
                                               'certain to'),1,
                             ifelse(is.na(likelyvand), NA_integer_, 0)),
         likelymug = ifelse(likelymug %in% c('Very likely','Fairly likely',
                                               'Certain to','fairly likely','very likely',
                                               'certain to'),1,
                             ifelse(is.na(likelymug), NA_integer_, 0)),
         likelyattack = ifelse(likelyattack %in% c('Very likely','Fairly likely',
                                             'Certain to'),1,
                            ifelse(is.na(likelyattack), NA_integer_, 0)),
         likelyburg = ifelse(likelyburg %in% c('Very likely','Fairly likely','Certain to',
                                               'fairly likely','very likely','certain to'),1,
                               ifelse(is.na(likelyburg), NA_integer_, 0)),
         likelyrape = ifelse(likelyrape %in% c('Fairly likely','Very likely','Certain to',
                                               'fairly likely','very likely','certain to'),1,
                             ifelse(is.na(likelyrape), NA_integer_, 0)),
         likelymolest = ifelse(likelymolest %in% c('Fairly likely','Very likely','Certain to'),1,
                             ifelse(is.na(likelymolest), NA_integer_, 0)),
         likelycarstolen = ifelse(likelycarstolen %in% c('fairly likely','very likely','certain to'),1,
                               ifelse(is.na(likelycarstolen), NA_integer_, 0)),
         likelycartheft = ifelse(likelycartheft %in% c('fairly likely','very likely','certain to'),1,
                                  ifelse(is.na(likelycartheft), NA_integer_, 0)),
         likelycarvand = ifelse(likelycarvand %in% c('fairly likely','very likely','certain to'),1,
                                 ifelse(is.na(likelycarvand), NA_integer_, 0)),
         commonteen = ifelse(commonteen %in% c('Very common','Fairly common',
                                               'fairly common','very common'),1,
                                ifelse(is.na(commonteen), NA_integer_, 0)),
         commongraff = ifelse(commongraff %in% c('Very common','Fairly common',
                                               'fairly common','very common'),1,
                             ifelse(is.na(commongraff), NA_integer_, 0)),
         commonvand = ifelse(commonvand %in% c('fairly common','very common'),1,
                              ifelse(is.na(commonvand), NA_integer_, 0)),
         probteen = ifelse(probteen %in% c('vbigprob','fairbigprob',
                                           'vbig problem','fbig problem',
                                           'fairly big prob','very big prob',
                                           'fairly big problem','very big problem'),1,
                             ifelse(is.na(probteen) | probteen == 'refusal, no answer', NA_integer_, 0)),
         probvand = ifelse(probvand %in% c('vbigprob','fairbigprob',
                                           'vbig problem','fbig problem',
                                           'fairly big prob','very big prob',
                                           'fairly big problem','very big problem'),1,
                           ifelse(is.na(probvand) | probvand == 'refusal, no answer', NA_integer_, 0)),
         probraceattacks = ifelse(probraceattacks %in% c('vbigprob','fairbigprob',
                                           'vbig problem','fbig problem',
                                           'fairly big prob','very big prob',
                                           'fairly big problem','very big problem'),1,
                           ifelse(is.na(probraceattacks) | probraceattacks == 'refusal, no answer', NA_integer_, 0)),
         probdrugs = ifelse(probdrugs %in% c('vbigprob','fairbigprob',
                                             'vbig problem','fbig problem',
                                             'fairly big prob','very big prob',
                                             'fairly big problem','very big problem'),1,
                                  ifelse(is.na(probdrugs) | probdrugs == 'refusal, no answer', NA_integer_, 0)),
         crimcausepolsent = crimcausepolsent,
         crimcausepolsent2 = crimcausepolsent2,
         crimcausepolsent3 = crimcausepolsent3,
         senttoolenient = ifelse(senttoolenient %in% c('little lenient','much lenient',
                                             'much too lenient','a little too lenient'),1,
                            ifelse(is.na(senttoolenient) | senttoolenient == '0', NA_integer_, 0)),
         juvtoolenient = ifelse(juvtoolenient %in% c('much too lenient','little too lenient',
                                                       'a little too lenient'),1,
                                 ifelse(is.na(juvtoolenient) | juvtoolenient == '0', NA_integer_, 0))) %>%
  mutate(age = case_when( 
           age > 15 & age < 25 ~ "16-24",
           age > 24 & age < 50 ~ "25-49",
           age > 49 & age < 65 ~ "50-64",
           age > 64 ~ "65+"),
         Date = paste0(year, "-01-01"),
         race = case_match(race,
                           'white' ~ 'White',
                           c('asian', 'black', 'other') ~ 'Non-white')) -> readforanalysis

# Weighted score for each year by demographic characteristics

readforanalysis %>%
  select(!year) %>%
  group_by(Date) %>%
  summarise(across(where(is.numeric), \(x) weighted.mean(x, weight, na.rm = TRUE))) %>%
  pivot_longer(-Date) %>%
  drop_na(value) %>%
  filter(name != "weight") %>%
  rename(Index = value, Varname = name) %>%
  mutate(Demographic = "All adults") %>%
  left_join(., readforanalysis %>%
              select(!year) %>%
              group_by(Date) %>%
              summarise(across(where(is.numeric), ~n())) %>%
              pivot_longer(-Date) %>%
              drop_na(value) %>%
              filter(name != "weight") %>%
              rename(n = value, Varname = name) %>%
              mutate(Demographic = "All adults")) %>%
  bind_rows(readforanalysis %>%
              select(!year) %>%
              drop_na(age) %>%
              group_by(age, Date) %>%
              summarise(across(where(is.numeric), \(x) weighted.mean(x, weight, na.rm = TRUE))) %>%
              pivot_longer(-c(Date, age)) %>%
              drop_na(value) %>%
              filter(name != "weight") %>%
              rename(Index = value, Varname = name, Demographic = age) %>%
              left_join(., readforanalysis %>%
                          select(!year) %>%
                          drop_na(age) %>%
                          group_by(age, Date) %>%
                          summarise(across(where(is.numeric), ~n())) %>%
                          pivot_longer(-c(Date, age)) %>%
                          drop_na(value) %>%
                          filter(name != "weight") %>%
                          rename(n = value, Varname = name, Demographic = age))) %>%
  bind_rows(readforanalysis %>%
              select(!year) %>%
              drop_na(gender) %>%
              group_by(gender, Date) %>%
              summarise(across(where(is.numeric), \(x) weighted.mean(x, weight, na.rm = TRUE))) %>%
              pivot_longer(-c(Date, gender)) %>%
              drop_na(value) %>%
              filter(name != "weight") %>%
              rename(Index = value, Varname = name, Demographic = gender) %>%
              left_join(., readforanalysis %>%
                          select(!year) %>%
                          drop_na(gender) %>%
                          group_by(gender, Date) %>%
                          summarise(across(where(is.numeric), ~n())) %>%
                          pivot_longer(-c(Date, gender)) %>%
                          drop_na(value) %>%
                          filter(name != "weight") %>%
                          rename(n = value, Varname = name, Demographic = gender))) %>%
  bind_rows(readforanalysis %>%
              select(!year) %>%
              drop_na(race) %>%
              group_by(race, Date) %>%
              summarise(across(where(is.numeric), \(x) weighted.mean(x, weight, na.rm = TRUE))) %>%
              pivot_longer(-c(Date, race)) %>%
              drop_na(value) %>%
              filter(name != "weight") %>%
              rename(Index = value, Varname = name, Demographic = race) %>%
              left_join(., readforanalysis %>%
                          select(!year) %>%
                          drop_na(race) %>%
                          group_by(race, Date) %>%
                          summarise(across(where(is.numeric), ~n())) %>%
                          pivot_longer(-c(Date, race)) %>%
                          drop_na(value) %>%
                          filter(name != "weight") %>%
                          rename(n = value, Varname = name, Demographic = race))) %>%
  select(Varname, Date, Index, Demographic, n) %>%
  mutate(Poll = "BCS / CSEW") -> OutPut


OutPut %>%
  filter(Varname %in% c('senttoolenient','crimcausepolsent','crimcausepolsent2','crimcausepolsent3',
                        'juvtoolenient')) %>%
  write.csv(., "/Users/matteo/Desktop/Prison Polling/Dyad Ratio Ready/CSEW/Punitiveness.csv", row.names = FALSE)

OutPut %>%
  filter(Varname %in% c('safewalkdark','worryburg','worrymug','worryrape',
                        'worrymolest','worryvand','worryattack','worryinsult',
                        'likelyvand','likelymug','likelyattack','likelyburg',
                        'likelyrape','likelymolest','commonteen','commongraff',
                        'safehomenight','worrycarstolen','worrycartheft','worrycarvand',
                        'likelycarstolen','likelycartheft','likelycarvand','commonvand',
                        'crimeincr','probteen','probvand','probraceattacks',
                        'probdrugs','worryraceattacks','crimeincrUK','crimeincrUK2',
                        'quallife','worryrape2','qualif2','crimeincr2','crimconincr')) %>%
  write.csv(., "/Users/matteo/Desktop/Prison Polling/Dyad Ratio Ready/CSEW/FearCrime.csv", row.names = FALSE)
